﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.IO;
using System.Text;
using System.Collections;

using CE.User;
using CE.Global;
using CE.Report;
using CE.Database;
using CE.InputOut;
using CE.Mail;

public partial class car_ci_documents_download : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Request.QueryString["refid"] != null)
            {
                pnlDocuments.Visible = false;
                txtCarId.Text = Request.QueryString["refid"].ToString();
                txtUserId.Text = Request.QueryString["uid"].ToString();
                txtProviderId.Text = Request.QueryString["pid"].ToString();
                LoadApplicationDetails();
                LoadUploadDocument();
                GetCarDocumentDetails();
            }
        }
    }

    protected void grvFileUpload_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            HyperLink hplk = (HyperLink)e.Row.FindControl("hplDownLoad");
            Label Path = (Label)e.Row.FindControl("lblPath");

            if (hplk != null)
            {
                hplk.NavigateUrl = "DownLoad/UploadNonGGHandler.ashx?FileName=" + Path.Text;
            }
        }
    }

    protected void btnExcelDownload_Click(object sender, EventArgs e)
    {
        Response.Redirect(hplExcelDownLoad.NavigateUrl);
    }

    public string GetReferenceNumber(string Number, string ProviderId)
    {
        string convertno = CE.Global.General.GetRefNumber(Convert.ToInt64(Number.ToString()));
        string _RefNo = "";
        if (ProviderId == "11")
        {
            _RefNo = "AZC" + convertno.ToString();
        }
        else if (ProviderId == "12")
        {
            _RefNo = "MSI" + convertno.ToString();
        }
        else if (ProviderId == "16")
        {
            _RefNo = "AXA" + convertno.ToString();
        }
        else if (ProviderId == "29")
        {
            _RefNo = "LMG" + convertno.ToString();
        }
        else if (ProviderId == "31")
        {
            _RefNo = "CTS" + convertno.ToString();
        }
        else if (ProviderId == "32")
        {
            _RefNo = "BKI" + convertno.ToString();
        }
        else if (ProviderId == "33")
        {
            _RefNo = "MTI" + convertno.ToString();
        }
        else if (ProviderId == "41")
        {
            _RefNo = "KUR" + convertno.ToString();
        }
        else if (ProviderId == "46")
        {
            _RefNo = "TMS" + convertno.ToString();
        }
        return _RefNo;
    }

    protected void btnInternal_Click(object sender, EventArgs e)
    {
        if (txtProviderId.Text != string.Empty && txtProviderId.Text != string.Empty)
        {
            Int64 ProviderId = Convert.ToInt64(txtProviderId.Text.Trim());
            Int64 CECarId = Convert.ToInt64(txtCarId.Text.Trim());
            string _fileName = "FileName";
            string Language = ddlExcelLanguage.Text;
            _fileName = lblUpRefNo.Text.Trim() + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
            GenerateNonGGExcel(CECarId, ProviderId, _fileName);
        }

    }

    public void GenerateNonGGExcel(Int64 CECarId, Int64 ProviderId, string FileName)
    {
        Label lblContent = new Label();
        StringBuilder strText = new StringBuilder("");
        string Language = ddlExcelLanguage.Text;
        if (Language == "EN")
        {
            strText.Append("<table><tr><td colspan='2'><h3>Car Insurance - Application Form.</h3></td></tr></table>");
        }
        else if (Language == "TH")
        {
            strText.Append("<table><tr><td colspan='2'><h3>ประกันภัยรถยนต์ - แบบฟอร์ม</h3></td></tr></table>");
        }
        DataSet _dsApplication = new DataSet();

        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        string _spName = "sprocCXCRMNonGGApplicationSelectItem";
        SortedList _spara = new SortedList();
        _spara.Add("@CECarId", CECarId);
        _spara.Add("@ProviderCompanyId", ProviderId);
        DataSet _dsUploadDocument = new DataSet();
        _dsApplication = _db.GetDataSet(_spName, _spara);
        string _html = "";
        if (_dsApplication.Tables[0].Rows.Count > 0)
        {

            #region Variable Declaration

            string Title, Name, DOB, Gender, Occupation, ContactNo, Email, PresentAddress, SubDistrict, District, Province, PostalCode, Insurer, Package, NoOfDriver;
            string InsuranceType, GarageType, CarModelYear, CarMakeModel, Beneficiary, VehicleType, SumInsured, NCB;
            string CurrentInsurer, CarPlate, CarPlateProvince,  ChassisNumber, AddDrivers, CommencementDate;
            string Premium, CTPL, CTPLAmount, TotalPremium, PaymentType, QuoteRefNo, Payment, ApplicationDate, TotalCollectedPremium, CustomerName;
            string CustomerType, CTPLStart;

            Title = ""; Name = ""; DOB = ""; Gender = ""; Occupation = ""; ContactNo = ""; Email = ""; PresentAddress = ""; SubDistrict = ""; District = "";
            Province = ""; PostalCode = ""; Insurer = ""; Package = ""; NoOfDriver = ""; InsuranceType = ""; GarageType = ""; CarModelYear = "";
            CarMakeModel = ""; Beneficiary = ""; VehicleType = ""; SumInsured = ""; NCB = "";
            CurrentInsurer = ""; CarPlate = ""; CarPlateProvince = ""; ChassisNumber = "";
            AddDrivers = ""; CommencementDate = ""; Premium = ""; CTPL = ""; CTPLAmount = ""; TotalPremium = ""; PaymentType = ""; CTPLStart = "";
            QuoteRefNo = ""; Payment = ""; ApplicationDate = ""; TotalCollectedPremium = ""; CustomerName = ""; CustomerType = "";
            //For Driver
            string DriverName1, DriverDOB1, DriverName2, DriverDOB2;
            DriverName1 = ""; DriverDOB1 = ""; DriverName2 = ""; DriverDOB2 = "";

            #endregion

            #region Assign Values

            Email = _dsApplication.Tables[0].Rows[0]["Email"].ToString();
            Title = _dsApplication.Tables[0].Rows[0]["Title"].ToString();
            Name = _dsApplication.Tables[0].Rows[0]["Name"].ToString();
            CustomerName = _dsApplication.Tables[0].Rows[0]["CustomerName"].ToString();
            CustomerType = _dsApplication.Tables[0].Rows[0]["CustomerTypeTH"].ToString();
            DOB = _dsApplication.Tables[0].Rows[0]["DOB"].ToString();
            Gender = _dsApplication.Tables[0].Rows[0]["Gender"].ToString();
            Occupation = _dsApplication.Tables[0].Rows[0]["Occupation"].ToString();
            ContactNo = _dsApplication.Tables[0].Rows[0]["ContactNo"].ToString();
            Beneficiary = _dsApplication.Tables[0].Rows[0]["Beneficiary"].ToString();
            PresentAddress = _dsApplication.Tables[0].Rows[0]["PresentAddress"].ToString();
            SubDistrict = _dsApplication.Tables[0].Rows[0]["SubDistrict"].ToString();
            District = _dsApplication.Tables[0].Rows[0]["District"].ToString();
            Province = _dsApplication.Tables[0].Rows[0]["Province"].ToString();
            PostalCode = _dsApplication.Tables[0].Rows[0]["PostalCode"].ToString();
            Insurer = _dsApplication.Tables[0].Rows[0]["CompanyName"].ToString();
            Package = _dsApplication.Tables[0].Rows[0]["Package"].ToString();
            NoOfDriver = _dsApplication.Tables[0].Rows[0]["NoOfDriver"].ToString();
            if (NoOfDriver.ToString() == "0")
            {
                AddDrivers = "No";
            }
            else
            {
                AddDrivers = "Yes";
            }
            InsuranceType = _dsApplication.Tables[0].Rows[0]["InsuranceType"].ToString();
            GarageType = _dsApplication.Tables[0].Rows[0]["GarageType"].ToString();
            CarModelYear = _dsApplication.Tables[0].Rows[0]["CarModelYear"].ToString();
            CarMakeModel = _dsApplication.Tables[0].Rows[0]["CarMakeModel"].ToString();
            ChassisNumber = _dsApplication.Tables[0].Rows[0]["ChassisNumber"].ToString();
            Beneficiary = _dsApplication.Tables[0].Rows[0]["Beneficiary"].ToString();
            VehicleType = _dsApplication.Tables[0].Rows[0]["VehicleType"].ToString();
            SumInsured = _dsApplication.Tables[0].Rows[0]["SumInsured"].ToString();
            NCB = _dsApplication.Tables[0].Rows[0]["NCB"].ToString();
            CurrentInsurer = _dsApplication.Tables[0].Rows[0]["CurrentInsurer"].ToString();
            CarPlate = _dsApplication.Tables[0].Rows[0]["CarPlate"].ToString();
            CarPlateProvince = _dsApplication.Tables[0].Rows[0]["ProvinceRegistration"].ToString();
            CommencementDate = _dsApplication.Tables[0].Rows[0]["CommencementDate"].ToString();
            Premium = _dsApplication.Tables[0].Rows[0]["Premium"].ToString();
            CTPL = _dsApplication.Tables[0].Rows[0]["CTPL"].ToString();
            CTPLAmount = _dsApplication.Tables[0].Rows[0]["CTPLAmount"].ToString();
            CTPLStart = _dsApplication.Tables[0].Rows[0]["CTPLStart"].ToString();
            TotalPremium = _dsApplication.Tables[0].Rows[0]["TotalPremium"].ToString();
            TotalCollectedPremium = _dsApplication.Tables[0].Rows[0]["TotalCollectedPremium"].ToString();
            PaymentType = _dsApplication.Tables[0].Rows[0]["PaymentType"].ToString();
            QuoteRefNo = _dsApplication.Tables[0].Rows[0]["QuoteRefNo"].ToString();
            Payment = _dsApplication.Tables[0].Rows[0]["Payment"].ToString();
            ApplicationDate = _dsApplication.Tables[0].Rows[0]["OrderDate"].ToString();

            if (AddDrivers == "Yes")
            {
                if (_dsApplication.Tables[1].Rows.Count > 0)
                {
                    if (Convert.ToInt16(NoOfDriver) > 0)
                    {
                        DriverName1 = _dsApplication.Tables[1].Rows[0]["FullName"].ToString();
                        DriverDOB1 = _dsApplication.Tables[1].Rows[0]["DOB"].ToString();
                    }
                    if (Convert.ToInt16(NoOfDriver) > 1)
                    {

                        DriverName2 = _dsApplication.Tables[1].Rows[1]["FullName"].ToString();
                        DriverDOB2 = _dsApplication.Tables[1].Rows[1]["DOB"].ToString();
                    }
                }
            }

            #endregion

            #region Generate Excel File
            if (Language == "TH")
            {
                _html += "<html><body><table width='60%' cellpadding='1' cellspacing='1' border='1'>";
                _html += "<tr><td width='20%' align='left' colspan='2'><b>รายละเอียดผู้ทำประกัน</b></td></tr>";
                _html += "<tr><td width='300' align='left'><b>ประเภทลูกค้า</b></td><td  align='left'>" + CustomerType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>คำนำหน้า</b></td><td  align='left'>" + Title + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ชื่อลูกค้าผู้ติดต่อ</b></td><td  align='left'>" + CustomerName + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ชื่อผู้ทำประกัน</b></td><td  align='left'>" + Name + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>วันเกิด</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy'  align='left'>" + DOB + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>เพศ</b></td><td  align='left'>" + Gender + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>อาชีพ</b></td><td  align='left'>" + Occupation + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>เบอร์ติดต่อ</b></td><td style='mso-number-format:\\@' align='left'>" + ContactNo + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ที่อยู่ปัจจุบัน</b></td><td  align='left'>" + PresentAddress + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>แขวง/ตำบล</b></td><td  align='left'>" + SubDistrict + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>เขต/อำเภอ</b></td><td  align='left'>" + District + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>จังหวัด</b></td><td  align='left'>" + Province + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>รหัสไปรษณีย์</b></td><td  align='left'>" + PostalCode + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>บริษัทประกัน</b></td><td  align='left'>" + Insurer + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>แพกเก็จ</b></td><td  align='left'>" + Package + "</td></tr>";

                _html += "<tr><td width='300' align='left'><b>ระบุผู้ขับขี่</b></td><td  align='left'>" + AddDrivers + "</td></tr>";
                if (AddDrivers == "Yes")
                {
                    _html += "<tr><td width='300' align='left'><b>จำนวนผู้ขับขี่</b></td><td  align='left'>" + NoOfDriver + "</td></tr>";
                }
                if (Convert.ToInt16(NoOfDriver) > 0)
                {
                    _html += "<tr><td width='300' align='left'><b>วันเกิด ผู้ขับขี่คนที่ 1</b></td><td  style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + DriverDOB1 + "</td></tr>";
                    _html += "<tr><td width='300' align='left'><b>ผู้ขับขี่คนที่ 1</b></td><td  align='left'>" + DriverName1 + "</td></tr>";
                }
                if (Convert.ToInt16(NoOfDriver) > 1)
                {
                    _html += "<tr><td width='300' align='left'><b>วันเกิด ผู้ขับขี่คนที่ 2</b></td><td  style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + DriverDOB2 + "</td></tr>";
                    _html += "<tr><td width='300' align='left'><b>ผู้ขับขี่คนที่ 2</b></td><td  align='left'>" + DriverName2 + "</td></tr>";
                }
                _html += "<tr><td width='300' align='left'><b>ประเภทประกัน</b></td><td  align='left'>" + InsuranceType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ประเภทอู่</b></td><td  align='left'>" + GarageType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ปีของรถ</b></td><td  align='left'>" + CarModelYear + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>รุ่น</b></td><td  align='left'>" + CarMakeModel + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ผู้รับผลประโยชน์</b></td><td  align='left'>" + Beneficiary + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ประเภทรถ</b></td><td  align='left'>" + VehicleType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ทุนประกัน</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00'  align='left'>" + SumInsured + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ส่วนลด NCB</b></td><td  align='left'>" + NCB + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>บริษัทประกันปัจจุบัน</b></td><td  align='left'>" + CurrentInsurer + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ทะเบียนรถ</b></td><td  align='left'>" + CarPlate + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>จังหวัดที่จดทะเบียน</b></td><td  align='left'>" + CarPlateProvince + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>หมายเลข Chassis</b></td><td  align='left'>" + ChassisNumber + "</td></tr>";
                _html += "<tr><td width='300'  align='left'><b>วันที่ พ.ร.บ.เริ่มคุ้มครอง</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy'  align='left'>" + CTPLStart + "</td></tr>";
                _html += "<tr><td width='300'  align='left'><b>วันที่เริ่มทำประกัน</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy'  align='left'>" + CommencementDate + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>เบี้ยประกันภาคสมัครใจ</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00' align='left'>" + Premium + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>พ.ร.บ.</b></td><td  align='left'>" + CTPL + "</td></tr>";
                if (CTPL == "Yes")
                {
                    _html += "<tr><td width='300' align='left'><b>จำนวน (พ.ร.บ.)</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00'  align='left'>" + CTPLAmount + "</td></tr>";
                }
                _html += "<tr><td width='300' align='left'><b>ราคารวม</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00'  align='left'>" + TotalPremium + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>วันที่ทำรายการ</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy\\ hh\\:mm\\ AM\\/PM'  align='left'>" + ApplicationDate + "</td></tr>";

                _html += "</table></body></html>";
            }
            else if (Language == "EN")
            {
                _html += "<html><body><table width='60%' cellpadding='1' cellspacing='1' border='1'>";
                _html += "<tr><td width='20%' align='left' colspan='2'><b>Details</b></td></tr>";
                _html += "<tr><td width='300' align='left'><b>Customer Type</b></td><td  align='left'>" + CustomerType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Title</b></td><td  align='left'>" + Title + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Customer Name</b></td><td  align='left'>" + CustomerName + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Registered Owner</b></td><td  align='left'>" + Name + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>DOB</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy'  align='left'>" + DOB + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Gender</b></td><td  align='left'>" + Gender + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Occupation/b></td><td  align='left'>" + Occupation + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ContactNo</b></td><td style='mso-number-format:\\@' align='left'>" + ContactNo + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>PresentAddress</b></td><td  align='left'>" + PresentAddress + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>SubDistrict</b></td><td  align='left'>" + SubDistrict + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>District</b></td><td  align='left'>" + District + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Province</b></td><td  align='left'>" + Province + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>PostalCode</b></td><td  align='left'>" + PostalCode + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Insurer</b></td><td  align='left'>" + Insurer + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Package</b></td><td  align='left'>" + Package + "</td></tr>";

                _html += "<tr><td width='300' align='left'><b>Additional Drivers</b></td><td  align='left'>" + AddDrivers + "</td></tr>";
                if (AddDrivers == "Yes")
                {
                    _html += "<tr><td width='300' align='left'><b>No. Of Driver</b></td><td  align='left'>" + NoOfDriver + "</td></tr>";
                }
                if (Convert.ToInt16(NoOfDriver) > 0)
                {
                    _html += "<tr><td width='300' align='left'><b>Driver DOB 1</b></td><td  style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + DriverDOB1 + "</td></tr>";
                    _html += "<tr><td width='300' align='left'><b>Driver Name 1</b></td><td  align='left'>" + DriverName1 + "</td></tr>";
                }
                if (Convert.ToInt16(NoOfDriver) > 1)
                {
                    _html += "<tr><td width='300' align='left'><b>Driver DOB 2</b></td><td  style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + DriverDOB2 + "</td></tr>";
                    _html += "<tr><td width='300' align='left'><b>Driver Name 2</b></td><td  align='left'>" + DriverName2 + "</td></tr>";
                }
                _html += "<tr><td width='300' align='left'><b>Insurance Type</b></td><td  align='left'>" + InsuranceType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Garage Type</b></td><td  align='left'>" + GarageType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>CarModel Year</b></td><td  align='left'>" + CarModelYear + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>CarMakeModel</b></td><td  align='left'>" + CarMakeModel + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Beneficiary</b></td><td  align='left'>" + Beneficiary + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>VehicleType</b></td><td  align='left'>" + VehicleType + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>SumInsured</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00'  align='left'>" + SumInsured + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>NCB</b></td><td  align='left'>" + NCB + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Current Insurer</b></td><td  align='left'>" + CurrentInsurer + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>CarPlate</b></td><td  align='left'>" + CarPlate + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>CarPlate Province</b></td><td  align='left'>" + CarPlateProvince + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>ChassisNumber</b></td><td  align='left'>" + ChassisNumber + "</td></tr>";
                _html += "<tr><td width='300'  align='left'><b>CTPL Start</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy'  align='left'>" + CTPLStart + "</td></tr>";
                _html += "<tr><td width='300'  align='left'><b>Commencement Date</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy'  align='left'>" + CommencementDate + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Premium</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00' align='left'>" + Premium + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>CMI</b></td><td  align='left'>" + CTPL + "</td></tr>";
                if (CTPL == "Yes")
                {
                    _html += "<tr><td width='300' align='left'><b>CMI Amount</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00'  align='left'>" + CTPLAmount + "</td></tr>";
                }
                _html += "<tr><td width='300' align='left'><b>Total Premium</b></td><td style='mso-number-format:\\#\\,\\#\\#0\\.00'  align='left'>" + TotalPremium + "</td></tr>";
                _html += "<tr><td width='300' align='left'><b>Application Date</b></td><td style='mso-number-format:dd\\-mmm\\-yyyy\\ hh\\:mm\\ AM\\/PM'  align='left'>" + ApplicationDate + "</td></tr>";

                _html += "</table></body></html>";
            }
            #endregion
        }
        lblInternalExcel.Text = _html;
        ExportGridView(FileName, lblInternalExcel);
    }

    public void ExportGridView(string fileName, Label lblContent)
    {
        string attachment = "attachment; filename=" + fileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        Response.Write(lblContent.Text);
        Response.End();
    }

    public void LoadApplicationDetails()
    {
        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        string _spName = "sprocCXCRMNonGGCarMoreDetailSelectSingleItem";
        SortedList _spara = new SortedList();
        _spara.Add("@CECarId", Convert.ToInt64(txtCarId.Text.Trim()));
        _spara.Add("@RefCEUserId", Convert.ToInt64(txtUserId.Text));
        _spara.Add("@ProviderCompanyId", Convert.ToInt64(txtProviderId.Text));
        DataSet _dsCarMoreDetails = new DataSet();
        _dsCarMoreDetails = _db.GetDataSet(_spName, _spara);
        if (_dsCarMoreDetails != null && _dsCarMoreDetails.Tables.Count > 0 && _dsCarMoreDetails.Tables[1].Rows.Count > 0)
        {
            lblUpRefNo.Text = _dsCarMoreDetails.Tables[1].Rows[0]["QuoteRefNo"].ToString();
            lblUpApplicantName.Text = _dsCarMoreDetails.Tables[1].Rows[0]["Name"].ToString();
            lblUpInsurer.Text = _dsCarMoreDetails.Tables[1].Rows[0]["CompanyName"].ToString();
            lblUpPlanName.Text = _dsCarMoreDetails.Tables[1].Rows[0]["Package"].ToString();
        }
        else
        {
            lblUpRefNo.Text = "";
            lblUpApplicantName.Text = "";
            lblUpInsurer.Text = "";
            lblUpPlanName.Text = "";
        }

    }

    public void LoadUploadDocument()
    {
        if (txtCarId.Text != string.Empty && txtUserId.Text != string.Empty && txtProviderId.Text != string.Empty)
        {
            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "sprocCXCRMNonGGCarFileUploadDetailSelectItemList";
            string Language = "EN";
            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", Convert.ToInt64(txtCarId.Text));
            _spara.Add("@ProviderCompanyId", Convert.ToInt64(txtProviderId.Text));
            _spara.Add("@Language", Language);
            DataSet _dsUploadDocument = new DataSet();
            _dsUploadDocument = _db.GetDataSet(_spName, _spara);
            if (_dsUploadDocument != null && _dsUploadDocument.Tables.Count > 3 && _dsUploadDocument.Tables[3].Rows.Count > 0)
            {
                pnlDocuments.Visible = true;
                grvFileUpload.Visible = true;
                grvFileUpload.DataSource = _dsUploadDocument.Tables[3];
                grvFileUpload.DataBind();
            }
            else
            {
                pnlDocuments.Visible = false;
                grvFileUpload.DataSource = null;
                grvFileUpload.DataBind();
            }
        }

    }

    public void GetCarDocumentDetails()
    {
        hplExcelDownLoad.NavigateUrl = string.Empty;
        hplExcelDownLoad.Visible = false;

        if (txtCarId.Text != string.Empty && txtUserId.Text != string.Empty && txtProviderId.Text != string.Empty)
        {
            MyIO _myIO = new MyIO();
            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "sprocCXCRMNonGGCarFileUploadDetailSelectItemList";
            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", Convert.ToInt64(txtCarId.Text));
            _spara.Add("@ProviderCompanyId", Convert.ToInt64(txtProviderId.Text));
            DataSet _dsCarDocumentDetails = new DataSet();
            _dsCarDocumentDetails = _db.GetDataSet(_spName, _spara);

            if (_dsCarDocumentDetails != null && _dsCarDocumentDetails.Tables.Count > 2)
            {
                if (_dsCarDocumentDetails.Tables[2].Rows.Count > 0)
                {
                    if (_dsCarDocumentDetails.Tables[2].Rows[0]["FileName"] != null
                        && _dsCarDocumentDetails.Tables[2].Rows[0]["FileName"].ToString() != string.Empty)
                    {
                        hplExcelDownLoad.NavigateUrl = "DownLoad/UploadNonGGHandler.ashx?FileName=" + _dsCarDocumentDetails.Tables[2].Rows[0]["FileName"].ToString();
                        hplExcelDownLoad.Visible = true;
                    }
                }
                else
                {
                    hplExcelDownLoad.NavigateUrl = "";
                    hplExcelDownLoad.Visible = true;
                }
            }
            else
            {
                hplExcelDownLoad.NavigateUrl = "";
                hplExcelDownLoad.Visible = true;
            }

        }
        else
        {
            hplExcelDownLoad.NavigateUrl = "";
            hplExcelDownLoad.Visible = true;
        }
    }

}
